package com.css.bjdt_core.annual.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.annual.entity.RptAnnual;
import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.cell.service.CustomSqlService;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.DateUtil;
import com.css.bjdt_core.util.PageQuerys;

@Service
public class AnnualService extends CustomSqlService {
	@Autowired
	private Dao dao;

	/**
	 * 分页查询统计年度报表
	 * @param cnd
	 * @return
	 */
	public Map queryPage(Condition cnd, int pageNum, int pageSize) {
		Map result = PageQuerys.ligerGridQuery(dao, RptAnnual.class, cnd, pageNum, pageSize);
		/*List<RptAnnual> rptAnnualList = (List<RptAnnual>) result.get("Rows");
		result.put("Rows", rptAnnualList);*/
		return result;
	}

	/**
	 * 新增统计年度报表
	 * @param record
	 * @return
	 */
	public RptAnnual insert(RptAnnual record) {
		if (StringUtils.isEmpty(record.getAnnualId())) {
			record.setAnnualId(CommonUtil.getRandomString(16).toUpperCase());
		}
		return dao.insert(record);
	}

	/**
	 * 更新统计年度报表
	 * @param record
	 * @return
	 */
	public int update(RptAnnual record) {
		return dao.updateIgnoreNull(record);
	}

	/**
	 * 根据id查询相关统计年度报表
	 * @param id
	 * @return
	 */
	public RptAnnual queryByID(String id) {
		return dao.fetch(RptAnnual.class, id);
	}

	public void deleteAnnual(String id) {
		dao.clear(RptAnnual.class, Cnd.where("ANNUALID", "=", id));
	}

	//获取年报和半年报的报表
	public List<RptAnnual> queryAllAnnual() {
		return dao.query(RptAnnual.class,
				Cnd.where("RPTATTRIBUTE", "=", "1").or("RPTATTRIBUTE", "=", "2").desc("ORDERID"));
	}

	//获取日常报表
	public List<RptAnnual> queryDailyAllAnnual() {
		return dao.query(RptAnnual.class, Cnd.where("RPTATTRIBUTE", "=", "3").desc("ORDERID"));
	}

	/**
	 * 查询当前年度id
	 * @return
	 */
	public String queryCurrentAnnualId() {
		String sqlStr = "select annualid from RPT_ANNUAL t where RPTATTRIBUTE = @rptAttribute "
				+ "and starttime >= str_to_date(@starttime,'%Y-%c-%d') and endtime <= str_to_date(@endtime,'%Y-%c-%d')";
		String rptAttribute = "1";
		if (DateUtil.getCurrMonth() >= 7) {
			rptAttribute = "2";
		}
		String currYearFirst = DateUtil.formatDate(DateUtil.getCurrYearFirst());
		String currYearLast = DateUtil.formatDate(DateUtil.getCurrYearLast());
		List<VarParam> varparams = new ArrayList<VarParam>();
		VarParam rptAttributeParam = new VarParam(null, null, "rptAttribute", rptAttribute);
		VarParam currYearFirstParam = new VarParam(null, null, "starttime", currYearFirst);
		VarParam currYearLastParam = new VarParam(null, null, "endtime", currYearLast);
		varparams.add(rptAttributeParam);
		varparams.add(currYearFirstParam);
		varparams.add(currYearLastParam);
		return executeSql(sqlStr, Sqls.callback.str(), varparams.toArray(new VarParam[0])).getString();
	}

	/**
	 * 根据传入的年度查询出当前年度下是否存在模板
	 * @param isHalf 是否是半年报 true：半年报  false:全年报
	 * @return
	 */
	public String queryThisYearCount(String thisYear, boolean isHalf) {
		if (isHalf) {
			//MYSQL
			/*String strSql = "select count(*) from "
					+ "(select ANNUALID,date_format(t.endtime,'%Y-%m-%d') as dateStr from RPT_ANNUAL t where t.RPTATTRIBUTE = '1') t1 "
					+ "where dateStr like @thisYear";*/
			//ORACLE
			String strSql = "select count(*) from "
					+ "(select ANNUALID,to_char(t.endtime,'yyyy-mm-dd') as dateStr from RPT_ANNUAL t where t.RPTATTRIBUTE = '1') t1 "
					+ "where dateStr like @thisYear";
			Sql sql = Sqls.create(strSql);
			sql.setCallback(Sqls.callback.str());
			sql.params().set("thisYear", thisYear + "%");
			dao.execute(sql);
			return sql.getObject(String.class);
		} else {
			//MYSQL
			/*String strSql = "select count(*) from "
					+ "(select ANNUALID,date_format(t.endtime,'%Y-%m-%d') as dateStr from RPT_ANNUAL t where t.RPTATTRIBUTE = '2') t1 "
					+ "where dateStr like @thisYear";*/
			//ORACLE
			String strSql = "select count(*) from "
					+ "(select ANNUALID,to_char(t.endtime,'yyyy-mm-dd') as dateStr from RPT_ANNUAL t where t.RPTATTRIBUTE = '2') t1 "
					+ "where dateStr like @thisYear";
			Sql sql = Sqls.create(strSql);
			sql.setCallback(Sqls.callback.str());
			sql.params().set("thisYear", thisYear + "%");
			dao.execute(sql);
			return sql.getObject(String.class);
		}
	}

	public String getAnnualIdByTime() {
		String strSql = "select annualId from (select * from RPT_ANNUAL where RPTATTRIBUTE in ('1','2') order by ENDTIME desc) where rownum=1";
		Sql sql = Sqls.create(strSql);
		sql.setCallback(Sqls.callback.str());
		dao.execute(sql);
		return sql.getObject(String.class);
	}

	/**
	 * get last Three years annualIds
	 * @param annualId  current annualId
	 * @author lyj
	 * @return
	 */
	public List<RptAnnual> getLastThreeYearsAnnualIds(String annualId) {
		String strSql = "select distinct annualid,name,starttime from RPT_ANNUAL  where extract(year from starttime) >= "
				+ " (SELECT  to_number(extract(year from sysdate))-3  FROM   RPT_ANNUAL   WHERE   ANNUALID = @annualId ) "
				+ "  and starttime <(SELECT    starttime    FROM   RPT_ANNUAL   WHERE   ANNUALID = @annualId)"
				+ "  and RPTATTRIBUTE in(1,2)   order by starttime desc";
		Sql sql = Sqls.create(strSql);
		sql.params().set("annualId", annualId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(java.sql.Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptAnnual> RptAnnualList = new ArrayList<RptAnnual>();
				while (rs.next()) {
					RptAnnual rptAnnual = new RptAnnual();
					rptAnnual.setAnnualId(rs.getString("ANNUALID"));
					rptAnnual.setName(rs.getString("NAME"));

					RptAnnualList.add(rptAnnual);
				}
				return RptAnnualList;
			}
		});

		dao.execute(sql);
		return sql.getList(RptAnnual.class);

	}

	public String getAnnualIdByYear(String year) {
		String strSql = "select t1.annualId from (select t.*,to_char(t.starttime,'yyyy-mm-dd') as startTimeStr from RPT_ANNUAL t where t.rptattribute = '2') t1 where startTimeStr like @year";
		Sql sql = Sqls.create(strSql);
		sql.params().set("year", year + "%");
		sql.setCallback(Sqls.callback.str());
		dao.execute(sql);
		return sql.getObject(String.class);
	}
}
